﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;
using PMS.IRepository;
using PMS.Models.Base;
using PMS.Utils;

namespace PMS.Repository
{
    public class BaseUserRepository : BaseRepository<BaseUser>, IBaseUserRepository
    {
        #region 公共方法


        public int Add(BaseUser user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into BaseUser(");
            strSql.Append("UserName,PassWord,RegisterDate,LastLoginDate,IsDel,UpdateBy,UpdateDate,DeleteBy,DeleteDate)");
            strSql.Append(" values (");
            strSql.Append("@UserName,@PassWord,@RegisterDate,@LastLoginDate,@IsDel,@UpdateBy,@UpdateDate,@DeleteBy,@DeleteDate)");
            strSql.Append(";select @@IDENTITY");
            using (DbConn)
            {
                return DbConn.Execute(strSql.ToString(), user);
            }
        }

        public int Edit(BaseUser user)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update BaseUser set ");
            strSql.Append("UserName=@UserName,");
            strSql.Append("PassWord=@PassWord,");
            strSql.Append("RegisterDate=@RegisterDate,");
            strSql.Append("LastLoginDate=@LastLoginDate,");
            strSql.Append("IsDel=@IsDel,");
            strSql.Append("UpdateBy=@UpdateBy,");
            strSql.Append("UpdateDate=@UpdateDate,");
            strSql.Append("DeleteBy=@DeleteBy,");
            strSql.Append("DeleteDate=@DeleteDate");
            strSql.Append(" where Id=@Id");
            using (DbConn)
            {
                return DbConn.Execute(strSql.ToString(), user);
            }
        }

        public BaseUser Dtl(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 Id,UserName,PassWord,RegisterDate,LastLoginDate,IsDel,UpdateBy,UpdateDate,DeleteBy,DeleteDate from BaseUser ");
            strSql.Append(" where Id=@Id");
            using (DbConn)
            {
                return DbConn.QueryFirstOrDefault<BaseUser>(strSql.ToString(), new { Id = id });
            }
        }

        public bool IsExists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from BaseUser");
            strSql.Append(" where Id=@Id");
            using (DbConn)
            {
                return DbConn.ExecuteScalar(strSql.ToString(), new { Id = id }).ToInt32() > 0;
            }
        }


        public int Delete(int id, string deleteBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Update BaseUser  Set ");
            strSql.Append(" IsDel=1, ");
            strSql.Append(" DeleteBy=@DeleteBy, ");
            strSql.Append(" DeleteDate=GetDate() ");
            strSql.Append(" where Id=@Id");
            using (DbConn)
            {
                return DbConn.Execute(strSql.ToString(), new { Id = id, DeleteBy = deleteBy });
            }
        }

        public int Delete(int[] ids, string deleteBy)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Update BaseUser  Set ");
            strSql.Append(" IsDel=1, ");
            strSql.Append(" DeleteBy=@DeleteBy, ");
            strSql.Append(" DeleteDate=GetDate() ");
            strSql.Append(" where Id in @Ids");
            using (DbConn)
            {
                return DbConn.Execute(strSql.ToString(), new { Id = ids.ToArray(), DeleteBy = deleteBy });
            }
        }

        public List<BaseUser> GetList()
        {
            string strSql = "Select * from BaseUser";
            using (DbConn)
            {
                return DbConn.Query<BaseUser>(strSql).ToList();
            }
        }

        public List<BaseUser> GetList(Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            Dictionary<string, object> param = new Dictionary<string, object>();
            GetStrWhereAndParam(ht, sb, param);
            string strSql = "Select * from BaseUser where 1=1 " + sb;
            using (DbConn)
            {
                return DbConn.Query<BaseUser>(strSql, param).ToList();
            }
        }

        public int GetCount(Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            Dictionary<string, object> param = new Dictionary<string, object>();
            GetStrWhereAndParam(ht, sb, param);
            string strSql = "Select * from BaseUser where 1=1 " + sb;
            using (DbConn)
            {
                return DbConn.ExecuteScalar(strSql, param).ToInt32();
            }
        }


        public void GetStrWhereAndParam(Hashtable ht, StringBuilder sb, Dictionary<string, object> param)
        {
            if (ht.IsNullOrEmptyByKey("IsDel"))
            {
                sb.Append(" And IsDel=@IsDel");
                param.Add("IsDel", ht["IsDel"]);
            }
            else
            {
                sb.Append(" And IsDel=0");
            }

            if (ht.IsNullOrEmptyByKey("Enable"))
            {
                sb.Append(" And Enable=@Enable");
                param.Add("Enable", ht["Enable"]);
            }

            if (ht.IsNullOrEmptyByKey("Title"))
            {
                sb.Append(" And Title Like @Title");
                param.Add("Title", "%" + ht["Title"] + "%");
            }

            if (ht.IsNullOrEmptyByKey("Sym"))
            {
                sb.Append(" And Sym=@Sym");
                param.Add("Sym", ht["Sym"]);
            }

            if (ht.IsNullOrEmptyByKey("UserName"))
            {
                sb.Append(" And UserName=@UserName");
                param.Add("UserName", ht["UserName"]);
            }

        }

        /// <summary>
        /// 验证用户是否存在
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="passWord"></param>
        /// <returns></returns>
        public bool IsExistUser(string userName, string passWord)
        {
            string strSql = "Select count(1) from BaseUser where IsDel<1 And UserName=@UserName And PassWord=@PassWord";
            Dictionary<string, object> param = new Dictionary<string, object>
            {
                {"@UserName", userName}, {"@PassWord", passWord}
            };
            using (DbConn)
            {
                return DbConn.ExecuteScalar(strSql, param).ToInt32() > 0;
            }
        }

        #endregion

        #region 扩展方法

        public List<BaseUser> GetList(Hashtable ht, string orderFiled, string sort, int pageIndex, int pageSize, out int counts)
        {

            string strWhere = string.Empty;
            Dictionary<string, object> param = new Dictionary<string, object>();
            if (ht.IsNullOrEmptyByKey("RoleName"))
            {
                param.Add("@RoleName", ht["RoleName"]);
                strWhere += " And RoleName like @RoleName ";
            }
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" Select * from ( ");
            strSql.Append(" Select *, ");
            strSql.Append("Row_Number() over( order by ");
            strSql.Append(orderFiled);
            strSql.Append(" ");
            strSql.Append(sort);
            strSql.Append(" ) rowNum from BaseUser Where IsDel<1");
            strSql.Append(strWhere);
            strSql.Append(") as res");
            strSql.Append(" Where rowNum BETWEEN ");
            strSql.Append((pageIndex - 1) * pageSize + 1);
            strSql.Append(" And ");
            strSql.Append(pageIndex * pageSize);

            using (DbConn)
            {
                string countSql = "Select Count(1) from BaseUser " + strWhere;
                counts = DbConn.ExecuteScalar(countSql, param).ToInt32();
                return DbConn.Query<BaseUser>(strSql.ToString(), param).ToList();
            }


        }

        public BaseUser GetBaseUser(string userName, string passWord)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select * from BaseUser where IsDel<1");
            strSql.Append(" And UserName=@UserName");
            strSql.Append(" And PassWord=@PassWord");
            Dictionary<string, object> param = new Dictionary<string, object>
            {
                {"@UserName", userName}, {"@PassWord", passWord}
            };
            using (DbConn)
            {
                return DbConn.QueryFirstOrDefault<BaseUser>(strSql.ToString(), param);
            }
        }

        #endregion

    }
}